//Documentation complete
/**
 * @author Jason Funk
 * @version 2009MR05
 */

package broker;

import problemDomain.*;
import static problemDomain.ErrorBean.throwError;
import static problemDomain.ErrorBean.ErrorCode.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 * <code>Broker</code> for <code>ReservationBean</code>.
 */
public class ReservationBroker extends Broker<ReservationBean> {
	
	/* Singleton Constructor */
	
	private static ReservationBroker broker;
	
	private ReservationBroker () throws ErrorBean {
		insertSQL =
		updateSQL =
		deleteReservationSQL = null;
	}
	
	/**
	 * Retrieves an instance of <code>ReservationBroker</code>.
	 * @return an instance of <code>ReservationBroker</code>
	 * @throws ErrorBean if there is an error opening a database connection
	 */
	public synchronized static ReservationBroker getBroker () throws ErrorBean {
		if (broker == null)
			broker = new ReservationBroker();
		return broker;
	}
	
	/* END Singleton Constructor */
	
	
	/* Prepared Statements */
	
	private PreparedStatement insertSQL;
	private PreparedStatement getInsertSQL () throws ErrorBean {
		return prepare(insertSQL, "INSERT INTO reservation SET fk_patron_id = ?, fk_employee_id = ?, first_name = ?, last_name = ?, phone = ?, description = ?, number_people = ?, date_placed = ?, reservation_date = ?;");
	}
	
	private PreparedStatement updateSQL;
	private PreparedStatement getUpdateSQL () throws ErrorBean {
		return prepare(updateSQL, "UPDATE reservation SET first_name = ?, last_name = ?, phone = ?, description = ?, number_people = ?, date_placed = ?, reservation_date = ? WHERE reservation_id = ?;");
	}
	
	private PreparedStatement deleteReservationSQL;
	private PreparedStatement getDeleteReservationSQL () throws ErrorBean {
		return prepare(deleteReservationSQL, "DELETE FROM reservation WHERE reservation_id = ?;");
	}
	
	/* END Prepared Statements */
	
	
	/* Operational Methods */
	
	@Override
	public synchronized List<ReservationBean> listAll() throws ErrorBean {
		List<ReservationBean> search = new ArrayList<ReservationBean>();
		ReservationBean rb;
		
		StringBuffer sql = new StringBuffer("SELECT *, time(date_placed) placedTime," +
				" time(reservation_date) resTime FROM reservation WHERE reservation_date > NOW() ORDER BY reservation_date;");
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					rb = new ReservationBean();
					rb.setReservationID(results.getInt("reservation_id"));
					rb.setFirstname(results.getString("first_name"));
					rb.setLastname(results.getString("last_name"));
					rb.setPhone(results.getString("phone"));
					Date date = results.getDate("date_placed");
					String time = results.getString("placedTime");
					rb.setDatePlaced(convertDateToCalendar(date, time));
					date = results.getDate("reservation_date");
					time = results.getString("resTime");
					rb.setReservationDate(convertDateToCalendar(date, time));
										
					rb.setDescription(results.getString("description"));
					rb.setNumberPeople(results.getInt("number_people"));
					
					EmployeeAccountBroker eab = EmployeeAccountBroker.getBroker();
					EmployeeAccountBean searchEmployeeAccountBean = new EmployeeAccountBean();
					
					int employee_id = results.getInt("fk_employee_id");
					if (employee_id > 0) {
						searchEmployeeAccountBean.setID(results.getInt("fk_employee_id"));
						List<EmployeeAccountBean> employeeList = eab.search(searchEmployeeAccountBean);
						if(employeeList == null || employeeList.size() == 0) {
							rb.setCreatedBy(null);
						}
						else {
							rb.setCreatedBy(employeeList.get(0));
						}
					}
					else
						rb.setCreatedBy(null);
					
					PatronAccountBroker pab = PatronAccountBroker.getBroker();
					PatronAccountBean searchPatronAccountBean = new PatronAccountBean();
					searchPatronAccountBean.setId(results.getInt("fk_patron_id"));
					
					List<PatronAccountBean> patronList = pab.search(searchPatronAccountBean);
					if(patronList == null || patronList.size() == 0) {
						rb.setPatron(null);
					}
					else {
						rb.setPatron(patronList.get(0));
					}
										
					search.add(rb);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
		
	}

	@Override
	public synchronized boolean persist(ReservationBean rb) throws ErrorBean {
		rb.isValid();
		
		int reservation_id = rb.getReservationID();
		
		PatronAccountBean patron = rb.getPatron();
		int patron_id = -1;
		if (patron != null)
			patron_id = rb.getPatron().getId();
		
		EmployeeAccountBean employee = rb.getCreatedBy();
		int employee_id = -1;
		if (employee != null)
			employee_id = rb.getCreatedBy().getID();
		String first_name = rb.getFirstname();
		String last_name = rb.getLastname();
		String phone = rb.getPhone();
		String description = rb.getDescription();
		int number_people = rb.getNumberPeople();
		Calendar dateP = rb.getDatePlaced();
		Calendar resDate = rb.getReservationDate();
		Timestamp date_placed = new Timestamp(dateP.getTimeInMillis());
		Timestamp reservation_date = new Timestamp(resDate.getTimeInMillis());
		
		
		
		if (reservation_id == 0) {
			try {
				PreparedStatement insertSQL = getInsertSQL();
				if (patron_id > 0)
					insertSQL.setInt(1, patron_id);
				else 
					insertSQL.setNull(1, Types.INTEGER);
				if (employee_id > 0)
					insertSQL.setInt(2, employee_id);
				else
					insertSQL.setNull(2, Types.INTEGER);
				insertSQL.setString(3, first_name);
				insertSQL.setString(4, last_name);
				insertSQL.setString(5, phone);
				insertSQL.setString(6, description);
				insertSQL.setInt(7, number_people);
				insertSQL.setTimestamp(8, date_placed);
				insertSQL.setTimestamp(9, reservation_date);
				insertSQL.execute();
				return true;
			} catch(SQLException e) {
				throwError(
					BROKER_INSERT_ERROR,
					e.getMessage(),
					this);
			}
		} else if (reservation_id > 0) {
			try {
				PreparedStatement updateSQL = getUpdateSQL();
				updateSQL.setString(1, first_name);
				updateSQL.setString(2, last_name);
				updateSQL.setString(3, phone);
				updateSQL.setString(4, description);
				updateSQL.setInt(5, number_people);
				updateSQL.setTimestamp(6, date_placed);
				updateSQL.setTimestamp(7, reservation_date);
				updateSQL.setInt(8, reservation_id);
				updateSQL.execute();
				return true;
			} catch(SQLException e) {
				throwError(
					BROKER_UPDATE_ERROR,
					e.getMessage(),
					this);
			}
		}
		return false;
	}

	@Override
	public synchronized boolean remove(ReservationBean rb) throws ErrorBean
	{
		int reservation_id = rb.getReservationID();
		
		try {
			PreparedStatement deleteReservationSQL = getDeleteReservationSQL();
			deleteReservationSQL.setInt(1, reservation_id);
			deleteReservationSQL.execute();
			return true;
		} catch(SQLException e) {
			throwError(
				BROKER_DELETE_ERROR,
				e.getMessage(),
				this);
			return false;
		}
	}

	@Override
	public synchronized List<ReservationBean> search(ReservationBean searchBean) throws ErrorBean {
		
		List<ReservationBean> search = new ArrayList<ReservationBean>();
		ReservationBean rb;
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT *, time(date_placed) placedTime," +
				" time(reservation_date) resTime FROM reservation WHERE reservation_date > NOW()");
		
		//Search by patron id
		RegisteredPatronAccountBean patron = (RegisteredPatronAccountBean) searchBean.getPatron();
		int patron_id = 0;
		if (patron != null)
			patron_id = patron.getId();
		
		if (patron_id > 0) {
			sql.append(" AND fk_patron_id = " + patron_id);
		}
		
		//Search by first name
		String first_name = searchBean.getFirstname();
		if (!"".equals(first_name) && first_name != null) {
			sql.append(" AND first_name LIKE '%" + first_name + "%'");
		}
		
		//Search by last name
		String last_name = searchBean.getLastname();
		if (!"".equals(last_name) && last_name != null) {
			sql.append(" AND last_name LIKE '%" + last_name + "%'");
		}
		//Search by phone number
		String phone = searchBean.getPhone();
		if (!"".equals(phone) && phone != null) {
			sql.append(" AND phone LIKE '%" + phone + "%'");
		}

		//Search by reservation date
		Calendar resDate = searchBean.getReservationDate();
		if (resDate != null) {
			Date reservation_date = new Date(resDate.getTimeInMillis());
			System.out.println(reservation_date.toString());
			sql.append(" AND DATE(reservation_date) = '" + reservation_date.toString() + "'");
		}
		
		sql.append(" ORDER BY reservation_date;");
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					rb = new ReservationBean();
					rb.setReservationID(results.getInt("reservation_id"));
					rb.setFirstname(results.getString("first_name"));
					rb.setLastname(results.getString("last_name"));
					rb.setPhone(results.getString("phone"));
					
					//Convert Dates to Calendar objects
					Calendar datePlaced = convertDateToCalendar(results.getDate("date_placed"), 
																results.getString("placedTime"));
					rb.setDatePlaced(datePlaced);
					Calendar reservationDate = convertDateToCalendar(results.getDate("reservation_date"),
																	results.getString("resTime"));
					rb.setReservationDate(reservationDate);
					
					rb.setDescription(results.getString("description"));
					rb.setNumberPeople(results.getInt("number_people"));
					
					EmployeeAccountBroker eab = EmployeeAccountBroker.getBroker();
					EmployeeAccountBean searchEmployeeAccountBean = new EmployeeAccountBean();
					int employee_id = results.getInt("fk_employee_id");
					if (employee_id > 0) {
						searchEmployeeAccountBean.setID(results.getInt("fk_employee_id"));
						List<EmployeeAccountBean> employeeList = eab.search(searchEmployeeAccountBean);
						if(employeeList == null || employeeList.size() == 0) {
							rb.setCreatedBy(null);
						}
						else {
							rb.setCreatedBy(employeeList.get(0));
						}
					}
					else
						rb.setCreatedBy(null);
					
					PatronAccountBroker pab = PatronAccountBroker.getBroker();
					PatronAccountBean searchPatronAccountBean = new PatronAccountBean();
					searchPatronAccountBean.setId(results.getInt("fk_patron_id"));
					
					List<PatronAccountBean> patronList = pab.search(searchPatronAccountBean);
					if(patronList == null || patronList.size() == 0) {
						rb.setPatron(null);
					}
					else {
						rb.setPatron(patronList.get(0));
					}
										
					search.add(rb);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}
	
	/* END (Broker) Methods */
	
	
	/* Methods */
	
	/**
	 * Searches the database for a ReservationBean based on its date.
	 * @param searchBean the ReservationBean being searched for
	 * @param searchType the type of search to perform
	 * @return search an ReservationBean List of search results
	 * @throws ErrorBean
	 */
	public synchronized List<ReservationBean> search(ReservationBean searchBean, String searchType) throws ErrorBean {

		List<ReservationBean> search = new ArrayList<ReservationBean>();
		ReservationBean rb;
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT *, time(date_placed) placedTime," +
				" time(reservation_date) resTime FROM reservation");
		
		Calendar reservation_date = searchBean.getReservationDate();
		
		if (!"".equals(reservation_date) && reservation_date != null) {
			sql.append(" WHERE YEAR(reservation_date) = " + reservation_date.get(Calendar.YEAR));
			if(searchType.equals("time")) {			
				sql.append(" AND TIME(reservation_date) = " + reservation_date.get(Calendar.HOUR_OF_DAY));
			}
			else if(searchType.equals("day")) {
				sql.append(" AND MONTH(reservation_date) = " + reservation_date.get(Calendar.MONTH));
				sql.append(" AND DAY(reservation_date) = " + reservation_date.get(Calendar.DAY_OF_MONTH));
			}
			else if(searchType.equals("week")) {
				sql.append(" AND WEEK(reservation_date) = " + reservation_date.get(Calendar.WEEK_OF_YEAR));
			}
			else if(searchType.equals("month")) {
				sql.append(" AND MONTH(reservation_date) = " + reservation_date.get(Calendar.MONTH));
			}
		}
		
		sql.append(';');
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					rb = new ReservationBean();
					rb.setReservationID(results.getInt("reservation_id"));
					rb.setFirstname(results.getString("first_name"));
					rb.setLastname(results.getString("last_name"));
					rb.setPhone(results.getString("phone"));
					
					//Convert Dates to Calendar objects
					Calendar datePlaced = convertDateToCalendar(results.getDate("date_placed"),
															results.getString("placedTime"));
					rb.setDatePlaced(datePlaced);					
					Calendar reservationDate = convertDateToCalendar(results.getDate("reservation_date"),
																	results.getString("resTime"));
					rb.setReservationDate(reservationDate);
					
					rb.setDescription(results.getString("description"));
					rb.setNumberPeople(results.getInt("number_people"));
					
					EmployeeAccountBroker eab = EmployeeAccountBroker.getBroker();
					EmployeeAccountBean searchEmployeeAccountBean = new EmployeeAccountBean();
					searchEmployeeAccountBean.setID(results.getInt("fk_employee_id"));
					
					List<EmployeeAccountBean> employeeList = eab.search(searchEmployeeAccountBean);
					if(employeeList == null || employeeList.size() == 0) {
						rb.setCreatedBy(null);
					}
					else {
						rb.setCreatedBy(employeeList.get(0));						
					}
					
					PatronAccountBroker pab = PatronAccountBroker.getBroker();
					PatronAccountBean searchPatronAccountBean = new PatronAccountBean();
					searchPatronAccountBean.setId(results.getInt("fk_patron_id"));
					
					List<PatronAccountBean> patronList = pab.search(searchPatronAccountBean);
					if(patronList == null || patronList.size() == 0) {
						rb.setPatron(null);
					}
					else {
						rb.setPatron(patronList.get(0));
					}
										
					search.add(rb);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}
	
	/* END Methods */
	
	
	/* Local Methods */
	
	/**
	 * Converts a Date object to a Calendar object.
	 * @param date The Date to convert
	 * @return c the converted Calendar
	 */
	private Calendar convertDateToCalendar(Date date, String time) {
		Calendar c = Calendar.getInstance();
		c.setTime(date);
		String [] fields = time.split(":");
		c.set(Calendar.HOUR, Integer.parseInt(fields[0]));
		c.set(Calendar.MINUTE, Integer.parseInt(fields[1]));
		return c;
	}
	
	/* END Local Methods */
	
}

